package com.vincent.druid.parser;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLLimit;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.builder.SQLSelectBuilder;
import com.alibaba.druid.sql.builder.impl.SQLSelectBuilderImpl;
import com.alibaba.druid.util.JdbcConstants;

import java.util.List;

/**
 * @author vincent
 * @date 2019/4/16
 * @description 查询sql构建器
 */
public class SQLSelectBuilderHelper {
    public static void main(String[] args) {
        SQLSelectBuilder builder = new SQLSelectBuilderImpl(JdbcConstants.MYSQL);
        /**
         * 设置查询字段
         */
        builder.select("name", "age", "password");
        /**
         * 表
         */
        builder.from("userinfo");

        builder.where("name like '文%' and age=18");
        // oracle不支持，设置会报错
        builder.limit(10, 0);

        builder.orderBy("age asc", "name desc");


        /**
         * sql解析出来的查询树
         */
        SQLSelectStatement sqlSelectStatement = builder.getSQLSelectStatement();
        /**
         * 获取查询部分
         */
        SQLSelect select = sqlSelectStatement.getSelect();
        /**
         * 获取最外层的order by， 在getQueryBlock并不存在
         */
        SQLOrderBy orderBy = select.getOrderBy();
        /**
         * 获取查询块
         */
        SQLSelectQueryBlock queryBlock = select.getQueryBlock();
        // 获取where条件，解析重点
        SQLExpr where = queryBlock.getWhere();
        SQLBinaryOpExpr sqlexpr = (SQLBinaryOpExpr) where;
        SQLBinaryOperator operator = sqlexpr.getOperator();
        // 判断是否是关系符号， = like > < 之类
        boolean relational = operator.isRelational();
        if (!relational) {
            // 说明含有多个查询条件，需要遍历左有子节点
            SQLExpr left = sqlexpr.getLeft();
            if (left instanceof SQLBinaryOpExpr) {
                SQLBinaryOperator operator1 = ((SQLBinaryOpExpr) left).getOperator();
                boolean relational1 = operator1.isRelational();
                if (relational1) {
                    SQLExpr left1 = ((SQLBinaryOpExpr) left).getLeft();
                    if (left1 instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) left1;
                        identifierExpr.setName("ID");
                    }
                }
            }
            System.out.println(left);
        }
        /**
         * 获取查询字段
         */
        List<SQLSelectItem> selectList = queryBlock.getSelectList();
        for (SQLSelectItem sqlSelectItem : selectList) {
            // 别名
            String alias = sqlSelectItem.getAlias();
            // 表达式
            SQLExpr expr = sqlSelectItem.getExpr();
            if (expr instanceof SQLIdentifierExpr) {
                // 就是普通的列名
                SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) expr;
                // 列名
                String name = identifierExpr.getName();
            }  else if (expr instanceof SQLAggregateExpr) {
                // 带函数的列，如count， max， to_date
                SQLAggregateExpr aggregateExpr = (SQLAggregateExpr) expr;
                // 函数名
                String methodName = aggregateExpr.getMethodName();
                // 参数列表
                List<SQLExpr> arguments = aggregateExpr.getArguments();

            }
        }

        /**
         * 获取分页limit， 其中oracle, sqlserver之类不支持limit操作
         */
        SQLLimit limit = queryBlock.getLimit();
        String sql = builder.toString();
        System.out.println(sql);


        String sqlstr = "select * from (select name, age from userinfo where age > 18 or name like '蔡徐%')";

        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sqlstr, JdbcConstants.MYSQL);
        for (SQLStatement sqlStatement : sqlStatements) {
            SQLSelectStatement sqlStatement1 = (SQLSelectStatement) sqlStatement;
            System.out.println(sqlStatement);
        }

    }
}
